"""
本文件实现数据的存储
"""
import time
import traceback
import spider
import pymysql


def get_con():
    """

    :return: 数据库的连接
    """
    # 建立连接
    host = "localhost"
    users = "root"
    password = "wql000218"
    db = "cov"
    charset = "utf8"
    conn = pymysql.connect(host=host, user=users, password=password, db=db, charset=charset)

    # 建立游标
    cursor = conn.cursor()
    return conn, cursor


def update_china_history():
    """

    :return: 更新数据中的历史数据
    """
    conn, cursor = get_con()
    data = spider.get_china_data()[0]
    try:
        # sql跟新语句

        sql = "insert into c_history values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) "
        sql_query = "select curConfirm from c_history where upadatetime=%s"
        print(f"{time.asctime()}开始更新历史数据")
        for k, v in data.items():
            if not cursor.execute(sql_query, k):
                cursor.execute(sql,
                               [k, v.get("curConfirm"), v.get("crued"), v.get("died"), v.get("confirmedRelative")
                                   , v.get("asymptomaticRelative"), v.get("curedRelative")
                                   , v.get("diedRelative"), v.get("overseasInputRelative"), v.get("nativeRelative")])
        conn.commit()  # 提交到数据库执行
        print(f"{time.asctime()}历史数据更新完毕")
    except:
        # 打印异常信息
        traceback.print_exc()
        # 发生错误时回滚
        conn.rollback()
    finally:
        # 关闭数据库
        conn.close()


def upadate_now():
    """

    :return: 更新全国的最新数据
    """

    conn, cursor = get_con()
    data = spider.get_china_data()[1]
    try:
        sql1 = "truncate table c_now"
        sql = "insert into c_now(updatetime, confirmed, cured, died, " \
              "confirmedRelative, asymptomaticRelative, curedRelative, " \
              "diedRelative, overseasInputRelative, nativeRelative, curConfirm," \
              " curLocalConfirm, curOverseasInput, asymptomatic) " \
              "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        sql_query = "select curConfirm from c_now where updatetime = %s"
        if not cursor.execute(sql_query, data[0]):
            print("开始更新当天数据")
            cursor.execute(sql1)
            cursor.execute(sql, data)
            conn.commit()
            print("更新完毕")
        else:
            print("已经时最新数据")
    except:
        # 打印异常信息
        traceback.print_exc()
        # 发生错误时回滚
        conn.rollback()
    finally:
        # 关闭数据库
        conn.close()


def upadate_province_details():
    """

    :return: 更新每个省的数据
    """
    conn, cursor = get_con()
    data = spider.get_china_data()[2]
    try:
        sql = "insert into c_province(updatetime, province, confirmedRelative, " \
              "diedRelative, curedRelative, asymptomaticRelative, " \
              "nativeRelative, overseasInputRelative,confirmed,curConfirm) " \
              "values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        sql_delete = "truncate table c_province "
        cursor.execute(sql_delete)
        print("开始更新各省最新数据")
        for item in data:
            cursor.execute(sql, item)
        conn.commit()
        print("更新完成")
    except:
        # 打印异常信息
        traceback.print_exc()
        # 发生错误时回滚
        conn.rollback()
    finally:
        # 关闭数据库
        conn.close()


def upadate_city_details():
    """

    :return: 更新各个城市数据
    """
    conn, cursor = get_con()
    data = spider.get_china_data()[3]
    try:
        sql = "insert into c_city(updatetime, province, city, confirmedRelative, asymptomaticRelative, nativeRelative)" \
              "values (%s,%s,%s,%s,%s,%s)"
        sql_query = "select %s = (select updatetime from c_city order by id desc limit 1)"
        cursor.execute(sql_query, data[0][0])
        if not cursor.fetchone()[0]:
            print("更新各市最新数据")
            for item in data:
                cursor.execute(sql, item)
            conn.commit()
            print("更新完成")
        else:
            print("已经是最新数据")
    except:
        # 打印异常信息
        traceback.print_exc()
        # 发生错误时回滚
        conn.rollback()
    finally:
        # 关闭数据库
        conn.close()


def update_highDangerousAreas():
    """

    :return: 更新高风险区名单
    """
    conn,cursor = get_con()
    data = spider.get_china_data()[4]
    try:
        sql_delete = "truncate table c_highdangerousareas "
        sql_insert = "insert into c_highdangerousareas (province, city, area) " \
                     "values(%s,%s,%s)"
        print("开始更新全国高风险区名单")
        cursor.execute(sql_delete)
        for item in data:
            cursor.execute(sql_insert,item)
        conn.commit()
        print("更新完成")
    except:
        # 打印异常信息
        traceback.print_exc()
        # 发生错误时回滚
        conn.rollback()
    finally:
        # 关闭数据库
        conn.close()


def update_middleDangerousAreas():
    """

    :return: 更新高风险区名单
    """
    conn,cursor = get_con()
    data = spider.get_china_data()[5]
    try:
        sql_delete = "truncate table c_middleDangerousAreas "
        sql_insert = "insert into c_middleDangerousAreas (province, city, area) " \
                     "values(%s,%s,%s)"
        print("开始更新全国低风险区名单")
        cursor.execute(sql_delete)
        for item in data:
            cursor.execute(sql_insert,item)
        conn.commit()
        print("更新完成")
    except:
        # 打印异常信息
        traceback.print_exc()
        # 发生错误时回滚
        conn.rollback()
    finally:
        # 关闭数据库
        conn.close()